import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  // 创建用户表
  await knex.schema.createTable('users', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.string('username', 50).notNullable().unique();
    table.string('phone', 20).notNullable().unique();
    table.string('email', 100).nullable().unique();
    table.string('password_hash', 255).notNullable();
    table.string('avatar_url', 500).nullable();
    table.enum('status', ['active', 'inactive', 'suspended']).defaultTo('active');
    table.timestamp('last_login_at').nullable();
    table.timestamps(true, true);
    
    // 索引
    table.index(['username']);
    table.index(['phone']);
    table.index(['email']);
    table.index(['status']);
  });

  // 创建用户资料表
  await knex.schema.createTable('user_profiles', (table) => {
    table.uuid('user_id').primary().references('id').inTable('users').onDelete('CASCADE');
    table.date('birth_date').nullable();
    table.decimal('height', 5, 2).nullable().comment('身高，单位：cm');
    table.decimal('weight', 5, 2).nullable().comment('体重，单位：kg');
    table.enum('gender', ['male', 'female', 'other']).nullable();
    table.string('timezone', 50).defaultTo('Asia/Shanghai');
    table.json('preferences').nullable().comment('用户偏好设置');
    table.timestamps(true, true);
  });

  // 创建刷新令牌表
  await knex.schema.createTable('refresh_tokens', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('(lower(hex(randomblob(4))) || \'-\' || lower(hex(randomblob(2))) || \'-4\' || substr(lower(hex(randomblob(2))),2) || \'-\' || substr(\'89ab\',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || \'-\' || lower(hex(randomblob(6))))'));
    table.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
    table.string('token_hash', 255).notNullable();
    table.timestamp('expires_at').notNullable();
    table.string('device_info', 500).nullable();
    table.timestamp('last_used_at').nullable();
    table.timestamps(true, true);
    
    // 索引
    table.index(['user_id']);
    table.index(['token_hash']);
    table.index(['expires_at']);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('refresh_tokens');
  await knex.schema.dropTableIfExists('user_profiles');
  await knex.schema.dropTableIfExists('users');
}